package com.hotel;

import com.hotel.entity.*;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

//用户的Servlet
@WebServlet(urlPatterns = { "*.member" })
public class MemberServlet  extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //获取请求路径
        req.setCharacterEncoding("UTF-8");
        String path=req.getServletPath();
        System.out.println(path);
        if ("/logout.member".equals(path)){
            logout(req,resp);
        }
        else if ("/checkLogin.member".equals(path)) {
            checkLogin(req, resp);
        }
        else if ("/deleteSubscription.member".equals(path)) {
            deleteSubscription(req, resp); // 删除订单
        }
        else if ("/editMember.member".equals(path)) { //
            editMember(req, resp);//修改信息界面
        }
        else if ("/getBookidDetails.member".equals(path)) {
            getBookidDetails(req, resp);//客房信息
        }
        else if ("/deleteSubscriptionDetail.member".equals(path)) {
            deleteSubscriptionDetail(req, resp); // 删除订单
        }
        else if ("/deleteSubscription2.member".equals(path)) {
            deleteSubscription2(req, resp); // 删除订单
        }
        else if ("/memberQuery.member".equals(path)) {
            memberQuery(req, resp);
        }
        else if ("/deleteSubscription3.member".equals(path)) {
            deleteSubscription3(req, resp);
        }
        else if ("/bookidQuery.member".equals(path)) {
            bookidQuery(req, resp);
        }
        else if ("/checkout.member".equals(path)) {
            checkout(req, resp);
        }

        if ("/viewOrderDetails.member".equals(path)) {
            viewOrderDetails(req, resp);
        }
        else if ("/deleteSubscriptionDetail2.member".equals(path)) {
            deleteSubscriptionDetail2(req, resp); // 删除订单
        }
        if ("/viewResideDetails.member".equals(path)) {
            viewResideDetails(req, resp);
        }
    }



    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String path = req.getServletPath();
        System.out.println(path);
        if ("/register.member".equals(path)) {
            register(req, resp);
        }
        else if ("/login.member".equals(path)) {
            req.setCharacterEncoding("UTF-8");
            login(req, resp);
        }
        else if ("/login2.member".equals(path)) {
            login2(req, resp);
        }
        else if ("/addsubscription.member".equals(path)) {
            addsubscription(req, resp);
        } //新增订单
        else if ("/updateSubscription.member".equals(path)) {
            updateSubscription(req, resp);
        } //修改联系人信息
        else if ("/updateMember.member".equals(path)) {
            updateMember(req, resp);
        }//修改信息
        else if ("/updatePassword.member".equals(path)) {
            updatePassword(req, resp);
        }//更改密码
        else if ("/getAvailableBookids.member".equals(path)) { // 新增的处理请求
            getAvailableBookids(req, resp);
        }//新增房间
        else if ("/createSubscriptionDetails.member".equals(path)) { // 新增请求处理
            createSubscriptionDetails(req, resp);
        }//新增
        else if ("/adminLogin.member".equals(path)) {
            adminLogin(req, resp);
        }//管理员登录
        else if ("/querySubscription.member".equals(path)) {
            querySubscription(req, resp);
        }
        else if ("/updateMember2.member".equals(path)) {
            updateMember2(req, resp);
        }
        else if ("/addReside.member".equals(path)) {
            addReside(req, resp);
        }
        else if ("/UpdateOrderStatusServlet.member".equals(path)) {
            updateOrderStatus(req, resp);
        }


    }



    //用户注销功能
    protected void logout(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //注销等于将登录后存放在session中的member对象进行清除
        req.setCharacterEncoding("UTF-8");
        HttpSession session=req.getSession();
        session.removeAttribute("member");
        //session.invalidate();
        //用户注销成功返回首页
        resp.sendRedirect(req.getContextPath()+"/hotel/index.jsp");
    }

    // 用户登录功能
    protected void login2(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 获取前台传递的注册用户信息
        req.setCharacterEncoding("UTF-8");
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        System.out.println(username);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 根据用户信息校验
            ps = con.prepareStatement("SELECT * FROM member WHERE username=? AND pwd=?");
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();

            // 让tongyong=查询到的id
            if (rs.next()) {
                // 当用户登录成功后，考虑将用户信息存放到session中，便于后续某些业务逻辑的需求
                HttpSession session = req.getSession();
                Member member = new Member();
                member.setId(rs.getInt("id")); // 从rs中获取id
                tongyong = member.getId(); // 将id赋值给tongyong
                member.setUsername(rs.getString("username"));
                member.setName(rs.getString("name"));
                member.setEmail(rs.getString("email"));
                member.setPhone(rs.getString("phone"));
                session.setAttribute("member", member);
                // 用户名密码正确，表示登录成功
                resp.sendRedirect(req.getContextPath() + "/list.subscription");
                return;
            }
            // 用户名密码不正确
            req.setAttribute("msg", "用户名密码不正确！");
            req.getRequestDispatcher("hotel/index.jsp").forward(req, resp);
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //用户登录功能
    protected void login(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //获取前台传递的注册用户信息
        req.setCharacterEncoding("UTF-8");
        String username=req.getParameter("username");
        String password=req.getParameter("password");

        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un="root";
            String pwd="root";
            con= DriverManager.getConnection(url,un,pwd);
            //根据用户信息校验
            ps=con.prepareStatement("select * from member where username=? and pwd=? ");
            ps.setString(1,username);
            ps.setString(2,password);
            rs=ps.executeQuery();
            if (rs.next()){
                //当用户登录成功后，考虑将用户信息存放导session中，便于后续某些业务逻辑的需求
                HttpSession session=req.getSession();
                Member member=new Member();
                member.setId(rs.getInt("id"));
                member.setUsername(rs.getString("username"));
                member.setName(rs.getString("name"));
                member.setEmail(rs.getString("email"));
                member.setPhone(rs.getString("phone"));
                session.setAttribute("member",member);
                //用户名密码正确，表示登录成功
                resp.sendRedirect(req.getContextPath()+"/hotel/memberCenter.jsp");
                return;
            }
            //用户名密码不正确
            req.setAttribute("msg","用户名密码不正确！");
            req.getRequestDispatcher("hotel/login.jsp").forward(req,resp);
        }catch (Exception e){
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
    //用户注册功能
    protected void register(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("执行注册功能");
        req.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        String username = req.getParameter("username");
        String password = req.getParameter("pwd");
        String name = req.getParameter("name");
        String phone = req.getParameter("phone");
        String email = req.getParameter("email");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);
            ps = con.prepareStatement("select * from member where username=? ");
            ps.setString(1, username);
            rs = ps.executeQuery();
            if (!rs.next()) {
                con.setAutoCommit(false);
                ps = con.prepareStatement("insert into member (username,pwd,name,phone,email,regtime) values (?,?,?,?,?,now()) ");
                ps.setString(1, username);
                ps.setString(2, password);
                ps.setString(3, name);
                ps.setString(4, phone);
                System.out.println(phone);
                ps.setString(5, email);
                ps.executeUpdate();
                con.commit();
                resp.sendRedirect(req.getContextPath() + "/hotel/register-2.jsp");
                return;
            }

            req.setAttribute("msg", "用户名已存在！");
            req.getRequestDispatcher("hotel/register-1.jsp").forward(req, resp);
        } catch (Exception var36) {
            var36.printStackTrace();

            try {
                con.rollback();
            } catch (SQLException var35) {
                var35.printStackTrace();
            }

            return;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException var34) {
                    var34.printStackTrace();
                }
            }

            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException var33) {
                    var33.printStackTrace();
                }
            }

            if (con != null) {
                try {
                    con.close();
                } catch (SQLException var32) {
                    var32.printStackTrace();
                }
            }

        }

    }
    //新建订单
    protected void addsubscription(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        System.out.println("执行新增预定");
        String name = req.getParameter("name");
        System.out.println(name);
        String phone = req.getParameter("phone");
        String email = req.getParameter("email");
        System.out.println(name);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        HttpSession session = req.getSession();
        Member member = (Member) session.getAttribute("member");
        String na = null;
        if (member != null) {
            na = member.getUsername();
        }
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询联系人是否已存在，且状态是否为0
            ps = con.prepareStatement("SELECT * FROM subscription WHERE linkman=?");
            ps.setString(1, name);
            rs = ps.executeQuery();

            int maxNo = 1; // 默认值
            ps = con.prepareStatement("SELECT MAX(CAST(no AS UNSIGNED)) FROM subscription");
            ResultSet maxNoRs = ps.executeQuery();
            if (maxNoRs.next()) {
                maxNo = maxNoRs.getInt(1) + 1;
            }
            maxNoRs.close();

            // 检查联系人是否存在，也检查状态
            boolean exists = false;
            if (rs.next()) {
                exists = true;
                int status = rs.getInt("status");

            }

            SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmm");
            String timea = sdf.format(new Date());  // 例如：2505161230（10位）
            if (!exists) {
                con.setAutoCommit(false);
                ps = con.prepareStatement("INSERT INTO subscription (mid, no, linkman, email, phone, status, cretime, remark) VALUES (?, ?, ?, ?, ?, '2', NOW(), '无')");
                ps.setInt(1, tongyong);
                //ps.setString(2, timea);
                ps.setInt(2, maxNo);
                ps.setString(3, name);
                ps.setString(4, phone);
                ps.setString(5, email);
                ps.executeUpdate();
                con.commit();
                resp.sendRedirect(req.getContextPath() + "/list.subscription");
            } else {
                System.out.println("联系人已存在且状态不为0: " + name);
                resp.setContentType("text/html;charset=UTF-8");
                PrintWriter out = resp.getWriter();
                out.println("<script type='text/javascript'>");
                out.println("alert('联系人已存在！');");
                out.println("window.location.href='" + req.getContextPath() + "/list.subscription';");
                out.println("</script>");
                out.close();
            }
        } catch (Exception var36) {
            var36.printStackTrace();
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException var35) {
                var35.printStackTrace();
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException var34) {
                    var34.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException var33) {
                    var33.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException var32) {
                    var32.printStackTrace();
                }
            }
        }
    }
    int tongyong = 1;
    // 用户登录检测功能
    protected void checkLogin(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        HttpSession session = req.getSession();
        Member member = (Member) session.getAttribute("member");

        if (member != null) {
            // 用户已经登录，重定向到会员中心
            resp.sendRedirect(req.getContextPath() + "/hotel/memberCenter.jsp");
        } else {
            // 用户没有登录，弹出提示框并重定向到首页
            resp.setContentType("text/html;charset=UTF-8");
            PrintWriter out = resp.getWriter();
            out.println("<script type='text/javascript'>");
            out.println("alert('请先登录！');");
            out.println("window.location.href='" + req.getContextPath() + "/hotel/index.jsp';");
            out.println("</script>");
            out.close();
        }
    }

    int ssid=-1;
    // 用户删除订单功能
    protected void deleteSubscription(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String orderNo = req.getParameter("no");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSet rrs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 开始事务
            con.setAutoCommit(false);

            // 查询订阅 ID
            ps = con.prepareStatement("SELECT id FROM subscription WHERE no = ?");
            ps.setString(1, orderNo);
            rs = ps.executeQuery();

            String did = null;

            if (rs.next()) {
                did = rs.getString("id");

                // 查询所有相关的房间 ID
                ps = con.prepareStatement("SELECT rid FROM subscription_dtl WHERE sid= ?");
                ps.setString(1, did);
                rrs = ps.executeQuery();

                // 对每一个 rid 执行更新
                while (rrs.next()) {
                    String rrid = rrs.getString("rid");

// 第一步：获取 bookid 表中指定 id 的 no
                    String getNoSql = "SELECT no FROM bookid WHERE id = ?";
                    try (PreparedStatement getNoPs = con.prepareStatement(getNoSql)) {
                        getNoPs.setString(1, rrid);
                        try (ResultSet noRs = getNoPs.executeQuery()) {
                            if (noRs.next()) {
                                String bookidNo = noRs.getString("no");

                                // 第二步：根据 no 删除 reside 表中对应的数据
                                String deleteResideSql = "DELETE FROM reside WHERE dtlid = ?";
                                try (PreparedStatement deleteResidePs = con.prepareStatement(deleteResideSql)) {
                                    deleteResidePs.setString(1, bookidNo);
                                    deleteResidePs.executeUpdate();
                                }

                                // 第三步：更新 bookid 表的状态
                                String updateBookidSql = "UPDATE bookid SET status = 'o' WHERE id = ?";
                                try (PreparedStatement updateBookidPs = con.prepareStatement(updateBookidSql)) {
                                    updateBookidPs.setString(1, rrid);
                                    updateBookidPs.executeUpdate();
                                }
                            }
                        }
                    }

                }

                // 删除 subscription_dtl 中的所有记录
                ps = con.prepareStatement("DELETE FROM subscription_dtl WHERE sid = ?");
                ps.setString(1, did);
                ps.executeUpdate(); // 执行删除操作
            }

            // 执行删除操作
            ps = con.prepareStatement("DELETE FROM subscription WHERE no = ?");
            ps.setString(1, orderNo);
            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {
                // 提交事务
                con.commit();
                resp.setContentType("text/html;charset=UTF-8");
                PrintWriter out = resp.getWriter();
                out.println("<script type='text/javascript'>");
                out.println("alert('删除成功！');");
                out.println("window.location.href='" + req.getContextPath() + "/list.subscription';");
                out.println("</script>");
                out.close();
            } else {
                // 删除失败的处理
                con.rollback(); // 回滚事务
                req.setAttribute("msg", "删除失败，订单未找到！");
                req.getRequestDispatcher("/list.subscription").forward(req, resp);
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (con != null) {
                    con.rollback(); // 回滚事务
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        } finally {
            // 关闭所有资源
            if (rrs != null) {
                try {
                    rrs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.setAutoCommit(true); // 恢复到自动提交模式
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 更新用户订阅信息
    protected void updateSubscription(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String orderNo = req.getParameter("no");
        String linkman = req.getParameter("linkman");
        String phone = req.getParameter("phone");
        String email = req.getParameter("email");

        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 更新语句
            ps = con.prepareStatement("UPDATE subscription SET linkman = ?, phone = ?, email = ? WHERE no = ?");
            ps.setString(1, linkman);
            ps.setString(2, phone);
            ps.setString(3, email);
            ps.setString(4, orderNo);

            int rowsAffected = ps.executeUpdate();
            if (rowsAffected > 0) {
                resp.setContentType("text/html;charset=UTF-8");
                PrintWriter out = resp.getWriter();
                out.println("<script type='text/javascript'>");
                out.println("alert('修改成功！');");
                out.println("window.location.href='" + req.getContextPath() + "/list.subscription';");
                out.println("</script>");
                out.close();
            } else {
                // 更新失败的处理
                req.setAttribute("msg", "更新失败，订单未找到！");
                req.getRequestDispatcher("/list.subscription").forward(req, resp);
            }
        } catch (Exception e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "更新过程中出现错误");
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    protected void updateMember(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        HttpSession session = req.getSession();
        Member member = (Member) session.getAttribute("member");

        if (member == null) {
            resp.sendRedirect(req.getContextPath() + "/hotel/login.jsp");
            return;
        }

        int memberId = member.getId();
        String name = req.getParameter("name");
        String email = req.getParameter("email");
        String phone = req.getParameter("phone");

        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            ps = con.prepareStatement("UPDATE member SET name = ?, email = ?, phone = ? WHERE id = ?");
            ps.setString(1, name);
            ps.setString(2, email);
            ps.setString(3, phone);
            ps.setInt(4, memberId);
            ps.executeUpdate();

            // 更新 session 中的 member 对象
            member.setName(name);
            member.setEmail(email);
            member.setPhone(phone);
            session.setAttribute("member", member);
            resp.setContentType("text/html;charset=UTF-8");
            PrintWriter out = resp.getWriter();
            out.println("<script type='text/javascript'>");
            out.println("alert('修改成功！');");
            out.println("window.location.href='" + req.getContextPath() + "/list.subscription';");
            out.println("</script>");
            out.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 显示用户信息的功能
    protected void editMember(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        HttpSession session = req.getSession();
        Member member = (Member) session.getAttribute("member");

        if (member == null) {
            resp.sendRedirect(req.getContextPath() + "/hotel/login.jsp");
            return;
        }

        int memberId = member.getId();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            ps = con.prepareStatement("SELECT name, email, phone FROM member WHERE id = ?");
            ps.setInt(1, memberId);
            rs = ps.executeQuery();

            if (rs.next()) {
                // 将查询结果存储到请求属性中
                req.setAttribute("name", rs.getString("name"));
                req.setAttribute("email", rs.getString("email"));
                req.setAttribute("phone", rs.getString("phone"));
            }

            // 转发到修改信息的 JSP 页面
            req.getRequestDispatcher("hotel/editMember.jsp").forward(req, resp);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    protected void updatePassword(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        HttpSession session = req.getSession();
        Member member = (Member) session.getAttribute("member");

        if (member == null) {
            resp.sendRedirect(req.getContextPath() + "/hotel/login.jsp");
            return;
        }

        int memberId = member.getId();
        String oldPassword = req.getParameter("password");
        String newPassword = req.getParameter("newPassword");

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询当前密码
            ps = con.prepareStatement("SELECT pwd FROM member WHERE id = ?");
            ps.setInt(1, memberId);
            rs = ps.executeQuery();

            if (rs.next()) {
                String currentPassword = rs.getString("pwd");
                // 验证旧密码
                if (currentPassword.equals(oldPassword)) {
                    // 更新新密码
                    ps = con.prepareStatement("UPDATE member SET pwd = ? WHERE id = ?");
                    ps.setString(1, newPassword);
                    ps.setInt(2, memberId);
                    ps.executeUpdate();
                    resp.setContentType("text/html;charset=UTF-8");
                    PrintWriter out = resp.getWriter();
                    out.println("<script type='text/javascript'>");
                    out.println("alert('修改成功！');");
                    out.println("window.location.href='" + req.getContextPath() + "/list.subscription';");
                    out.println("</script>");
                    out.close();


                } else {
                    // 旧密码不正确
                    req.setAttribute("msg", "旧密码不正确！");
                    req.getRequestDispatcher("hotel/editPassword.jsp").forward(req, resp);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    protected void getBookidDetails(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String orderNo = req.getParameter("no"); // 获取订单号
//        System.out.println("id是"+orderNo);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询 subscription 表获取 did
            ps = con.prepareStatement("SELECT id FROM subscription WHERE no = ?");
            //让ssid=此处查到的id
            ps.setString(1, orderNo);
            rs = ps.executeQuery();

            if (rs.next()) {
                ssid = rs.getInt("id");
                int did = rs.getInt("id");

                // 查询 subscription_dtl 表获取 rid
                ps = con.prepareStatement("SELECT rid FROM subscription_dtl WHERE sid = ?");
                ps.setInt(1, did);
                rs = ps.executeQuery();

                // 存放结果
                List<BookidDetail> bookidDetails = new ArrayList<>();

                while (rs.next()) {
                    int rid = rs.getInt("rid");

                    // 查询 bookid 表获取 cid
                    ps = con.prepareStatement("SELECT subscription_dtl.id,sdate, edate,cid, no FROM bookid,subscription_dtl WHERE bookid.id = ? and subscription_dtl.sid=? and subscription_dtl.rid=bookid.id");
                    ps.setInt(1, rid);
                    ps.setInt(2, did);
                    ResultSet bookidRs = ps.executeQuery();

                    if (bookidRs.next()) {
                        int cid = bookidRs.getInt("cid");
                        String bookidNo = bookidRs.getString("no");

                        String bookidType = "";
                        if (cid == 1) {
                            bookidType = "活着";
                        } else if (cid == 2) {
                            bookidType = "百年孤独";
                        } else if (cid == 3) {
                            bookidType = "挪威的森林";
                        } else if (cid == 4) {
                            bookidType = "平凡的世界";
                        } else if (cid == 5) {
                            bookidType = "小王子";
                        } else if (cid == 6) {
                            bookidType = "悲惨世界";
                        } else if (cid == 7) {
                            bookidType = "追风筝的人";
                        } else if (cid == 8) {
                            bookidType = "围城";
                        } else if (cid == 9) {
                            bookidType = "海边的卡夫卡";
                        } else if (cid == 10) {
                            bookidType = "论语";
                        }


                            int subscriptionDetailId = bookidRs.getInt("id"); // 获取 id
                            String sdate = bookidRs.getString("sdate");
                            String edate = bookidRs.getString("edate");
                            System.out.println(did);
                            System.out.println(sdate+"mmmmm"+edate);
                            BookidDetail bookidDetail = new BookidDetail(subscriptionDetailId , bookidType, bookidNo,sdate, edate);
                            bookidDetails.add(bookidDetail);

                    }
                }

                // 将结果存入请求属性
                req.setAttribute("bookidDetails", bookidDetails);
                // 转发到显示房间信息的 JSP 页面
                req.getRequestDispatcher("/hotel/subscriptionDetail.jsp").forward(req, resp);
            } else {
                req.setAttribute("msg", "订单未找到！");
                req.getRequestDispatcher("/list.subscription").forward(req, resp);
            }
        } catch (Exception e) {
            e.printStackTrace();
            req.setAttribute("msg", "查询过程中出现错误！");
            req.getRequestDispatcher("/list.subscription").forward(req, resp);
        } finally {
            // 关闭资源
            if (rs != null) try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (con != null) try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
    // 用户删除预定细节功能
    protected void deleteSubscriptionDetail(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String detailId = req.getParameter("id"); // 获取预定细节的ID
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询与订阅细节关联的房间ID
            ps = con.prepareStatement("SELECT rid,sid FROM subscription_dtl WHERE id = ?");
            ps.setString(1, detailId);
            rs = ps.executeQuery();

            String bookidId = null;
            String sssid=null;
            if (rs.next()) {
                bookidId = rs.getString("rid");
                sssid = rs.getString("sid");
            }
            ps = con.prepareStatement("SELECT no FROM subscription WHERE id = ?");
            ps.setString(1, sssid);
            rs = ps.executeQuery();

            String nno = null;

            if (rs.next()) {
                nno = rs.getString("no");
            }
            // 如果找到了对应的房间ID，则更新房间状态
            if (bookidId != null) {


                String rrid = bookidId;

// 第一步：获取 bookid 表中指定 id 的 no
                String getNoSql = "SELECT no FROM bookid WHERE id = ?";
                try (PreparedStatement getNoPs = con.prepareStatement(getNoSql)) {
                    getNoPs.setString(1, rrid);
                    try (ResultSet noRs = getNoPs.executeQuery()) {
                        if (noRs.next()) {
                            String bookidNo = noRs.getString("no");

                            // 第二步：根据 no 删除 reside 表中对应的数据
                            String deleteResideSql = "DELETE FROM reside WHERE dtlid = ?";
                            try (PreparedStatement deleteResidePs = con.prepareStatement(deleteResideSql)) {
                                deleteResidePs.setString(1, bookidNo);
                                deleteResidePs.executeUpdate();
                            }

                            // 第三步：更新 bookid 表的状态
                            String updateBookidSql = "UPDATE bookid SET status = 'o' WHERE id = ?";
                            try (PreparedStatement updateBookidPs = con.prepareStatement(updateBookidSql)) {
                                updateBookidPs.setString(1, rrid);
                                updateBookidPs.executeUpdate();
                            }
                        }
                    }
                }


            }

            // 执行删除操作
            ps = con.prepareStatement("DELETE FROM subscription_dtl WHERE id = ?");
            ps.setString(1, detailId);
            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {

                req.setAttribute("msg", "删除成功！");
                resp.sendRedirect(req.getContextPath() + "/getBookidDetails.member?no=" + nno);
            } else {
                // 删除失败的处理
                req.setAttribute("msg", "删除失败，记录未找到！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            req.setAttribute("msg", "删除过程中出现错误！");
        } finally {
            // 关闭资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }



    protected void getAvailableBookids(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 获取房间类型和日期信息
        req.setCharacterEncoding("UTF-8");
        String bookidType = req.getParameter("rootType");
        String startDate = req.getParameter("startDate");
        String endDate = req.getParameter("endDate");

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<BookidType> availableBookids = new ArrayList<>(); // 存储可用房间的信息

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询可用房间
            ps = con.prepareStatement("SELECT no FROM bookid WHERE cid = ? AND status = 'o'");
            ps.setString(1, bookidType);
            rs = ps.executeQuery();

            while (rs.next()) {
                BookidType bookid = new BookidType();
                bookid.setNo(rs.getString("no")); // 设置房间编号
                bookid.setStartDate(startDate);   // 设置开始日期
                bookid.setEndDate(endDate);       // 设置结束日期
                availableBookids.add(bookid);        // 添加到列表中
            }

            for (BookidType bookid : availableBookids) {
                System.out.println("房间号: " + bookid.getNo());
                System.out.println("开始日期: " + bookid.getStartDate());
                System.out.println("结束日期: " + bookid.getEndDate());
            }
            // 将可用房间列表存入请求属性
            req.setAttribute("availableBookids", availableBookids);
            // 转发到 JSP 页面显示
            req.getRequestDispatcher("/hotel/createSubscriptionDtl-2.jsp").forward(req, resp);

        } catch (Exception e) {
            e.printStackTrace();
            req.setAttribute("msg", "查询过程中出现错误！");
            req.getRequestDispatcher("/hotel/error.jsp").forward(req, resp);
        } finally {
            if (rs != null) try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (con != null) try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
    protected void createSubscriptionDetails(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        HttpSession session = req.getSession();
        Member member = (Member) session.getAttribute("member");
        if (member == null) {
            resp.sendRedirect(req.getContextPath() + "/hotel/login.jsp");
            return;
        }

        String[] selectedBookids = req.getParameterValues("selectedBookids");
        if (selectedBookids == null || selectedBookids.length == 0) {
            req.setAttribute("msg", "请至少选择一个房间。");
            req.getRequestDispatcher("/hotel/createSubscriptionDtl-2.jsp").forward(req, resp);
            return;
        }

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 遍历所有选择的房间
            for (String bookidNo : selectedBookids) {
                ps = con.prepareStatement("SELECT id, cid FROM bookid WHERE no = ?");
                ps.setString(1, bookidNo);
                rs = ps.executeQuery();

                if (rs.next()) {
                    int bookidId = rs.getInt("id");
                    int bookId = rs.getInt("cid");

                    // 更新房间状态
                    ps = con.prepareStatement("UPDATE bookid SET status = 'c' WHERE no = ?");
                    ps.setString(1, bookidNo);
                    ps.executeUpdate();

                    // 查询房间价格
                    ps = con.prepareStatement("SELECT name FROM book WHERE id = ?");
                    ps.setInt(1, bookId);
                    ResultSet bookRs = ps.executeQuery();

                    if (bookRs.next()) {


                        String startDate = req.getParameter("startDate_" + bookidNo);
                        String endDate = req.getParameter("endDate_" + bookidNo);

                        // 插入到 subscription_dtl 表中
                        ps = con.prepareStatement("INSERT INTO subscription_dtl (rid, sid, sdate, edate) VALUES (?, ?, ?, ?)");
                        ps.setInt(1, bookidId);
                        ps.setInt(2, ssid);  // 使用 randomSid 作为整型参数
                        ps.setString(3, startDate);
                        ps.setString(4, endDate);
                        ps.executeUpdate();
                    }
                }
            }

            ps = con.prepareStatement("UPDATE subscription SET status = '1' WHERE id= ?");
            ps.setInt(1, ssid);
            ps.executeUpdate();

            ps = con.prepareStatement("SELECT no FROM subscription WHERE id = ?");
            ps.setInt(1, ssid);
            rs = ps.executeQuery();

            String nno = null;

            if (rs.next()) {
                nno = rs.getString("no");
            }

            // 预定成功后转发到成功页面
            req.setAttribute("successMessage", "预定成功！");
            req.setAttribute("subscriptionId", nno);  // 传递预定 ID
            req.getRequestDispatcher("/hotel/createSubscriptionDtl-3.jsp").forward(req, resp); // 跳转到成功页面
        } catch (Exception e) {
            e.printStackTrace();
            req.setAttribute("msg", "创建预定时发生错误！");
            req.getRequestDispatcher("/hotel/error.jsp").forward(req, resp);
        } finally {
            if (rs != null) try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (con != null) try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }

    // 管理员登录功能

    protected void adminLogin(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        System.out.println("开始");
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        System.out.println("开始"+username+password);

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            ps = con.prepareStatement("select * from manager where username=? and pwd=?");
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();

            if (rs.next()) {
                // 登录成功
                resp.sendRedirect(req.getContextPath() + "/hotel/manager/subscriptionQuery.jsp");
            } else {
                // 登录失败，提示信息
                System.out.println("失败");
                req.setAttribute("msg", "管理员用户名或密码错误！");
                req.getRequestDispatcher("/hotel/manager/login.jsp").forward(req, resp);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 在 MemberServlet.java 中添加查询方法

    // 处理订单查询
    protected void querySubscription(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String rootType = req.getParameter("rootType");
        String status = req.getParameter("status");
        String startDate = req.getParameter("startDate");
        String endDate = req.getParameter("endDate");
        String username = req.getParameter("username");
        String no = req.getParameter("no");
        System.out.println("*******");
        System.out.println(rootType);
System.out.println("----"+username);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8", "root", "root");

            // 获取rid
            Integer rid = null;
            List<Integer> ridList = new ArrayList<>(); // 存储所有可能的 rid

            if (!"0".equals(rootType)) {
                ps = con.prepareStatement("SELECT id FROM bookid WHERE cid = ?");
                ps.setInt(1, Integer.parseInt(rootType));
                rs = ps.executeQuery();
                while (rs.next()) {  // 使用 while 遍历所有结果，而不是 if
                    rid = rs.getInt("id");
                    ridList.add(rid); // 存入列表
                }
                rs.close();
                ps.close();
            }

// 获取 sid

            List<Integer> sidList = new ArrayList<>(); // 存储所有可能的 sid

            if ((startDate != null && !startDate.trim().isEmpty()) ||
                    (endDate != null && !endDate.trim().isEmpty()) ||
                    (ridList != null && !ridList.isEmpty())) {

                StringBuilder query = new StringBuilder("SELECT sid FROM subscription_dtl WHERE 1=1");
                if (startDate != null && !startDate.trim().isEmpty()) {
                    query.append(" AND sdate = ?");
                }
                if (endDate != null && !endDate.trim().isEmpty()) {
                    query.append(" AND edate = ?");
                }
                if (ridList != null && !ridList.isEmpty()) {
                    query.append(" AND rid IN (");
                    for (int i = 0; i < ridList.size(); i++) {
                        if (i > 0) query.append(",");
                        query.append("?");
                    }
                    query.append(")");
                }

                ps = con.prepareStatement(query.toString());
                int paramIndex = 1;

                if (startDate != null && !startDate.trim().isEmpty()) {
                    ps.setString(paramIndex++, startDate);
                }
                if (endDate != null && !endDate.trim().isEmpty()) {
                    ps.setString(paramIndex++, endDate);
                }
                if (ridList != null && !ridList.isEmpty()) {
                    for (Integer r : ridList) {
                        ps.setInt(paramIndex++, r);
                    }
                }

                rs = ps.executeQuery();
                while (rs.next()) {  // 使用 while 获取所有 sid
                    sidList.add(rs.getInt("sid"));
                }
                System.out.println(startDate + "#" + endDate + "#" + ridList + "#" + sidList);
                rs.close();
                ps.close();
            }
            Integer mid = null;
            if (username != null && !username.trim().isEmpty()) {
                ps = con.prepareStatement("SELECT id FROM member WHERE username = ?");
                ps.setString(1, username);
                rs = ps.executeQuery();
                if (rs.next()) {
                    mid = rs.getInt("id");
                }
                rs.close();
                ps.close();
            }
// 修改最终的 subscription 查询
            StringBuilder sql = new StringBuilder("SELECT * FROM subscription WHERE 1=1");
            if (!"3".equals(status)) {
                sql.append(" AND status = ").append(status);
            } else {
                sql.append(" AND status IN (0, 1, 2)");
            }
            if (no != null && !no.trim().isEmpty()) {
                sql.append(" AND no = '").append(no).append("'");
            }
            if (username != null && !username.trim().isEmpty()) {
                sql.append(" AND mid = ").append(mid);
            }
            if (!sidList.isEmpty()) {  // 如果 sidList 不为空
                sql.append(" AND id IN (");
                for (int i = 0; i < sidList.size(); i++) {
                    if (i > 0) sql.append(",");
                    sql.append(sidList.get(i));
                }
                sql.append(")");
            }

            ps = con.prepareStatement(sql.toString());
            rs = ps.executeQuery();

            List<SubscriptionInfo> list = new ArrayList<>();
            while (rs.next()) {
                SubscriptionInfo info = new SubscriptionInfo();
                info.setNo(rs.getString("no"));
                info.setLinkman(rs.getString("linkman"));
                info.setPhone(rs.getString("phone"));
                info.setEmail(rs.getString("email"));
                info.setStatus(rs.getInt("status"));
                list.add(info);
            }

            req.setAttribute("subscriptions", list);
            req.getRequestDispatcher("/hotel/manager/subscriptionList.jsp").forward(req, resp);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try { if (rs != null) rs.close(); } catch (Exception ignored) {}
            try { if (ps != null) ps.close(); } catch (Exception ignored) {}
            try { if (con != null) con.close(); } catch (Exception ignored) {}
        }
    }
    protected void deleteSubscription2(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String orderNo = req.getParameter("no");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSet rrs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 开始事务
            con.setAutoCommit(false);

            // 查询订阅 ID
            ps = con.prepareStatement("SELECT id FROM subscription WHERE no = ?");
            ps.setString(1, orderNo);
            rs = ps.executeQuery();

            String did = null;

            if (rs.next()) {
                did = rs.getString("id");

                // 查询所有相关的房间 ID
                ps = con.prepareStatement("SELECT rid FROM subscription_dtl WHERE sid= ?");
                ps.setString(1, did);
                rrs = ps.executeQuery();

                // 对每一个 rid 执行更新
                while (rrs.next()) {
                    String rrid = rrs.getString("rid");

// 第一步：获取 bookid 表中指定 id 的 no
                    String getNoSql = "SELECT no FROM bookid WHERE id = ?";
                    try (PreparedStatement getNoPs = con.prepareStatement(getNoSql)) {
                        getNoPs.setString(1, rrid);
                        try (ResultSet noRs = getNoPs.executeQuery()) {
                            if (noRs.next()) {
                                String bookidNo = noRs.getString("no");

                                // 第二步：根据 no 删除 reside 表中对应的数据
                                String deleteResideSql = "DELETE FROM reside WHERE dtlid = ?";
                                try (PreparedStatement deleteResidePs = con.prepareStatement(deleteResideSql)) {
                                    deleteResidePs.setString(1, bookidNo);
                                    deleteResidePs.executeUpdate();
                                }

                                // 第三步：更新 bookid 表的状态
                                String updateBookidSql = "UPDATE bookid SET status = 'o' WHERE id = ?";
                                try (PreparedStatement updateBookidPs = con.prepareStatement(updateBookidSql)) {
                                    updateBookidPs.setString(1, rrid);
                                    updateBookidPs.executeUpdate();
                                }
                            }
                        }
                    }

                }

                // 删除 subscription_dtl 中的所有记录
                ps = con.prepareStatement("DELETE FROM subscription_dtl WHERE sid = ?");
                ps.setString(1, did);
                ps.executeUpdate(); // 执行删除操作
            }

            // 执行删除操作
            ps = con.prepareStatement("DELETE FROM subscription WHERE no = ?");
            ps.setString(1, orderNo);
            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {
                // 提交事务
                req.getRequestDispatcher("/hotel/manager/subscriptionQuery.jsp").forward(req, resp);
            } else {
                // 删除失败的处理
                con.rollback(); // 回滚事务
                req.setAttribute("msg", "删除失败，订单未找到！");
                req.getRequestDispatcher("/list.subscription").forward(req, resp);
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (con != null) {
                    con.rollback(); // 回滚事务
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        } finally {
            // 关闭所有资源
            if (rrs != null) {
                try {
                    rrs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.setAutoCommit(true); // 恢复到自动提交模式
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    protected void memberQuery(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String username = req.getParameter("username");
        String name = req.getParameter("name");
        String email = req.getParameter("email");
        String phone = req.getParameter("phone");

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Members> memberList = new ArrayList<>();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            StringBuilder sql = new StringBuilder("SELECT id, username, pwd,name, email, phone, regtime FROM member WHERE 1=1");
            List<String> params = new ArrayList<>();

            if (username != null && !username.trim().isEmpty()) {
                sql.append(" AND username = ?");
                params.add(username);
            }
            if (name != null && !name.trim().isEmpty()) {
                sql.append(" AND name = ?");
                params.add(name);
            }
            if (email != null && !email.trim().isEmpty()) {
                sql.append(" AND email = ?");
                params.add(email);
            }
            if (phone != null && !phone.trim().isEmpty()) {
                sql.append(" AND phone = ?");
                params.add(phone);
            }

            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                ps.setString(i + 1, params.get(i));
            }

            rs = ps.executeQuery();
            while (rs.next()) {
                Members member = new Members();
                member.setId(rs.getInt("id"));
                member.setUsername(rs.getString("username"));
                member.setName(rs.getString("name"));
                member.setEmail(rs.getString("email"));
                member.setPhone(rs.getString("phone"));
                member.setRegtime(rs.getString("regtime"));
                member.setPwd(rs.getString("pwd")); // 新增：从数据库结果集中获取 ped
                memberList.add(member);

                System.out.println(phone+"执行");
            }

            req.setAttribute("memberList", memberList);
            req.getRequestDispatcher("/hotel/manager/memberList.jsp").forward(req, resp);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (ps != null) ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (con != null) con.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
    // 删除订阅和会员
    protected void deleteSubscription3(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String idStr = req.getParameter("id");
        System.out.println(idStr);
        System.out.println("执行了1");
        if (idStr != null && !idStr.isEmpty()) {
            int memberId = Integer.parseInt(idStr);
            Connection con = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
                String un = "root";
                String pwd = "root";
                con = DriverManager.getConnection(url, un, pwd);

                // 第一步：获取相关的订阅记录
                ps = con.prepareStatement("SELECT no FROM subscription WHERE mid = ?");
                ps.setInt(1, memberId);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String subscriptionNo = rs.getString("no");

                    // 将 subscriptionNo 作为请求参数传递给 delete 方法
                    req.setAttribute("no", subscriptionNo);
                    delete(req, resp);  // 调用 delete 函数
                }
System.out.println("执行了");
                // 第二步：删除会员记录
                ps = con.prepareStatement("DELETE FROM member WHERE id = ?");
                ps.setInt(1, memberId);
                ps.executeUpdate();

                resp.setContentType("text/html;charset=UTF-8");
                PrintWriter out = resp.getWriter();
                out.println("<script type='text/javascript'>");
                out.println("alert('删除成功！');");
                out.println("window.location.href='" + req.getContextPath() + "/hotel/manager/memberList.jsp';");
                out.println("</script>");
                out.close();


            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                // 清理数据库资源
                try {
                    if (rs != null) rs.close();
                    if (ps != null) ps.close();
                    if (con != null) con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            // 如果没有提供 id，重定向到错误页面或显示消息
            resp.sendRedirect(req.getContextPath() + "/hotel/manager/memberList.jsp");
        }
    }

    protected void delete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String orderNo = req.getParameter("no");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSet rrs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 开始事务
            con.setAutoCommit(false);

            // 查询订阅 ID
            ps = con.prepareStatement("SELECT id FROM subscription WHERE no = ?");
            ps.setString(1, orderNo);
            rs = ps.executeQuery();

            String did = null;

            if (rs.next()) {
                did = rs.getString("id");

                // 查询所有相关的房间 ID
                ps = con.prepareStatement("SELECT rid FROM subscription_dtl WHERE sid= ?");
                ps.setString(1, did);
                rrs = ps.executeQuery();

                // 对每一个 rid 执行更新
                while (rrs.next()) {
                    String rrid = rrs.getString("rid");

// 第一步：获取 bookid 表中指定 id 的 no
                    String getNoSql = "SELECT no FROM bookid WHERE id = ?";
                    try (PreparedStatement getNoPs = con.prepareStatement(getNoSql)) {
                        getNoPs.setString(1, rrid);
                        try (ResultSet noRs = getNoPs.executeQuery()) {
                            if (noRs.next()) {
                                String bookidNo = noRs.getString("no");

                                // 第二步：根据 no 删除 reside 表中对应的数据
                                String deleteResideSql = "DELETE FROM reside WHERE dtlid = ?";
                                try (PreparedStatement deleteResidePs = con.prepareStatement(deleteResideSql)) {
                                    deleteResidePs.setString(1, bookidNo);
                                    deleteResidePs.executeUpdate();
                                }

                                // 第三步：更新 bookid 表的状态
                                String updateBookidSql = "UPDATE bookid SET status = 'o' WHERE id = ?";
                                try (PreparedStatement updateBookidPs = con.prepareStatement(updateBookidSql)) {
                                    updateBookidPs.setString(1, rrid);
                                    updateBookidPs.executeUpdate();
                                }
                            }
                        }
                    }

                }

                // 删除 subscription_dtl 中的所有记录
                ps = con.prepareStatement("DELETE FROM subscription_dtl WHERE sid = ?");
                ps.setString(1, did);
                ps.executeUpdate(); // 执行删除操作
            }

            // 执行删除操作
            ps = con.prepareStatement("DELETE FROM subscription WHERE no = ?");
            ps.setString(1, orderNo);
            int rowsAffected = ps.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (con != null) {
                    con.rollback(); // 回滚事务
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        } finally {
            // 关闭所有资源
            if (rrs != null) {
                try {
                    rrs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.setAutoCommit(true); // 恢复到自动提交模式
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    // 修改会员信息功能
    protected void updateMember2(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");

        String username = req.getParameter("username");
        String password = req.getParameter("password");
        String name = req.getParameter("name");
        String email = req.getParameter("email");
        String phone = req.getParameter("phone");

        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);
            con.setAutoCommit(false);

            String sql = "UPDATE member SET pwd=?, name=?, email=?, phone=? WHERE username=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, password);
            ps.setString(2, name);
            ps.setString(3, email);
            ps.setString(4, phone);
            ps.setString(5, username);

            int rows = ps.executeUpdate();
            con.commit();

            if (rows > 0) {
                req.setAttribute("msg", "会员信息修改成功！");
            } else {
                req.setAttribute("msg", "修改失败，未找到对应用户！");
            }

            resp.setContentType("text/html;charset=UTF-8");
            PrintWriter out = resp.getWriter();
            out.println("<script type='text/javascript'>");
            out.println("alert('修改成功！');");
            out.println("window.location.href='" + req.getContextPath() + "/hotel/manager/memberQuery.jsp';");
            out.println("</script>");
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (con != null) con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (con != null) try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }

    protected void bookidQuery(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String bookidType = req.getParameter("rootType"); // 获取客房类型
        String status = req.getParameter("status"); // 获取状态
        String bookidNo = req.getParameter("no"); // 获取房间号
System.out.println( bookidType+status+bookidNo);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 构建查询条件
            StringBuilder query = new StringBuilder("SELECT * FROM bookid WHERE 1=1");

            // 根据用户输入的条件动态添加查询条件
            if (!"0".equals(bookidType)) {
                query.append(" AND cid = ?");  // 客房类型
            }
            if (!"0".equals(status)) {
                query.append(" AND status = ?");  // 状态
            }
            if (bookidNo != null && !bookidNo.trim().isEmpty()) {
                query.append(" AND no = ?");  // 房间号
            }

            ps = con.prepareStatement(query.toString());

            int paramIndex = 1;
            if (!"0".equals(bookidType)) {
                ps.setString(paramIndex++, bookidType);
            }
            if (!"0".equals(status)) {
                ps.setString(paramIndex++,status);
            }
            if (bookidNo != null && !bookidNo.trim().isEmpty()) {
                ps.setString(paramIndex++, bookidNo);
            }

            rs = ps.executeQuery();
            List<Bookid> bookidList = new ArrayList<>();

            while (rs.next()) {
                Bookid bookid = new Bookid();
                bookid.setCid(rs.getString("cid"));
                bookid.setNo(rs.getString("no"));
                bookid.setStatus(rs.getString("status"));

                bookidList.add(bookid);

            }

            req.setAttribute("bookidList", bookidList);
            req.getRequestDispatcher("/hotel/manager/bookList.jsp").forward(req, resp); // 将查询结果传递给前端

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    protected void checkout(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 获取前端传来的房间号
        req.setCharacterEncoding("UTF-8");
        String bookidNo = req.getParameter("bookidNo");
        if (bookidNo == null || bookidNo.isEmpty()) {
            resp.sendRedirect(req.getContextPath() + "/hotel/error.jsp");
            return;
        }

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询房间的ID
            ps = con.prepareStatement("SELECT id FROM bookid WHERE no = ?");
            ps.setString(1, bookidNo);
            rs = ps.executeQuery();
            if (rs.next()) {
                int bookidId = rs.getInt("id");

                // 删除对应的subscription_dtl记录
                ps = con.prepareStatement("DELETE FROM subscription_dtl WHERE rid = ?");
                ps.setInt(1, bookidId);
                ps.executeUpdate();


                // 更新房间状态为"未入住"
                ps = con.prepareStatement("UPDATE bookid SET status = 'o' WHERE no = ?");
                ps.setString(1, bookidNo);
                ps.executeUpdate();


                String deleteResideSql = "DELETE FROM reside WHERE dtlid = ?";
                try (PreparedStatement deleteResidePs = con.prepareStatement(deleteResideSql)) {
                    deleteResidePs.setString(1, bookidNo);  // 用查询出来的房间号作为参数
                    deleteResidePs.executeUpdate();
                }


                Bookid bookid = getBookidFromSession(req);
                if (bookid != null && bookid.getNo().equals(bookidNo)) {
                    bookid.setStatus("未入住");

                }

                ps.executeUpdate();
                resp.setContentType("text/html;charset=UTF-8");
                PrintWriter out = resp.getWriter();
                out.println("<script type='text/javascript'>");
                out.println("alert('修改成功！');");
                out.println("window.location.href='" + req.getContextPath() + "/hotel/manager/memberQuery.jsp';");
                out.println("</script>");
                out.close();
            } else {
                // 房间号无效
                req.setAttribute("msg", "无效的房间号！");
                req.getRequestDispatcher("hotel/memberCenter.jsp").forward(req, resp);
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 获取Session中的Bookid对象
    private Bookid getBookidFromSession(HttpServletRequest req) {
        HttpSession session = req.getSession();
        return (Bookid) session.getAttribute("bookid");
    }
    protected void viewOrderDetails(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String orderNo = req.getParameter("no");
        System.out.println("++++++++");
System.out.println(orderNo);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询订单信息
            ps = con.prepareStatement("SELECT * FROM subscription WHERE no = ?");
            ps.setString(1, orderNo);
            rs = ps.executeQuery();

            if (rs.next()) {
                int mid = rs.getInt("mid");

                // 根据mid查询会员用户名
                PreparedStatement psMember = con.prepareStatement("SELECT username FROM member WHERE id = ?");
                psMember.setInt(1, mid);
                ResultSet rsMember = psMember.executeQuery();
                String username = "";
                if (rsMember.next()) {
                    username = rsMember.getString("username");
                }

                // 将订单信息和会员信息设置到请求中
                req.setAttribute("orderNo", rs.getString("no"));
                req.setAttribute("regtime", rs.getTimestamp("cretime"));
                req.setAttribute("username", username);
                req.setAttribute("linkman", rs.getString("linkman"));
                req.setAttribute("phone", rs.getString("phone"));
                req.setAttribute("email", rs.getString("email"));

                // 处理订单状态
                int status = rs.getInt("status");
                String statusText = (status == 0) ? "取消" : (status == 1 ? "已确认" : "待确认");
                req.setAttribute("status", statusText);

                // 获取订单详细信息：查询subscription_dtl和bookid表
                int did = rs.getInt("id");

                // 查询 subscription_dtl 表获取 rid
                ps = con.prepareStatement("SELECT rid FROM subscription_dtl WHERE sid = ?");
                ps.setInt(1, did);
                rs = ps.executeQuery();

                // 存放房间信息
                List<BookidDetail> bookidDetails = new ArrayList<>();

                while (rs.next()) {
                    int rid = rs.getInt("rid");

                    // 查询 bookid 表获取 cid
                    ps = con.prepareStatement("SELECT cid, no FROM bookid WHERE id = ?");
                    ps.setInt(1, rid);
                    ResultSet bookidRs = ps.executeQuery();

                    if (bookidRs.next()) {
                        int cid = bookidRs.getInt("cid");
                        String bookidNo = bookidRs.getString("no");

                        String bookidType = "";
                        if (cid == 1) {
                            bookidType = "活着";
                        } else if (cid == 2) {
                            bookidType = "百年孤独";
                        } else if (cid == 3) {
                            bookidType = "挪威的森林";
                        } else if (cid == 4) {
                            bookidType = "平凡的世界";
                        } else if (cid == 5) {
                            bookidType = "小王子";
                        }
                        else if (cid == 6) {
                            bookidType = "悲惨世界";
                        }
                        else if (cid == 7) {
                            bookidType = "追风筝的人";
                        }
                        else if (cid == 8) {
                            bookidType = "围城";
                        }
                        else if (cid == 9) {
                            bookidType = "海边的卡夫卡";
                        }
                        else if (cid == 10) {
                            bookidType = "论语";
                        }

                        // 查询 subscription_dtl 表获取 sdate, edate, price
                        ps = con.prepareStatement("SELECT id ,sdate, edate FROM subscription_dtl WHERE sid = ?");
                        ps.setInt(1, did);
                        ResultSet subscriptionDtlRs = ps.executeQuery();
                        if (subscriptionDtlRs.next()) {
                            int id = subscriptionDtlRs.getInt("id");
                            String sdate = subscriptionDtlRs.getString("sdate");
                            String edate = subscriptionDtlRs.getString("edate");


                            BookidDetail bookidDetail = new BookidDetail(id, bookidType, bookidNo,  sdate, edate);
                            bookidDetails.add(bookidDetail);
                        }
                    }
                }

                // 将房间信息存入请求属性
                req.setAttribute("bookidDetails", bookidDetails);
            }

            // 转发到订单详情页面
            req.getRequestDispatcher("/hotel/manager/subscriptionDetail.jsp").forward(req, resp);

        } catch (Exception e) {
            e.printStackTrace();
            req.setAttribute("msg", "查询过程中出现错误！");
            req.getRequestDispatcher("/hotel/manager/subscriptionDetail.jsp").forward(req, resp);
        } finally {
            // 关闭资源
            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (ps != null) ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (con != null) con.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }

    protected void deleteSubscriptionDetail2(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String detailId = req.getParameter("id"); // 获取预定细节的ID
        String no = req.getParameter("no");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            // 查询与订阅细节关联的房间ID
            ps = con.prepareStatement("SELECT rid,sid FROM subscription_dtl WHERE id = ?");
            ps.setString(1, detailId);
            rs = ps.executeQuery();

            String bookidId = null;
            String sssid=null;
            if (rs.next()) {
                bookidId = rs.getString("rid");
                sssid = rs.getString("sid");
            }
            ps = con.prepareStatement("SELECT no FROM subscription WHERE id = ?");
            ps.setString(1, sssid);
            rs = ps.executeQuery();

            String nno = null;

            if (rs.next()) {
                nno = rs.getString("no");
            }
            // 如果找到了对应的房间ID，则更新房间状态
            if (bookidId != null) {

                String rrid =bookidId ;

// 第一步：获取 bookid 表中指定 id 的 no
                String getNoSql = "SELECT no FROM bookid WHERE id = ?";
                try (PreparedStatement getNoPs = con.prepareStatement(getNoSql)) {
                    getNoPs.setString(1, rrid);
                    try (ResultSet noRs = getNoPs.executeQuery()) {
                        if (noRs.next()) {
                            String bookidNo = noRs.getString("no");

                            // 第二步：根据 no 删除 reside 表中对应的数据
                            String deleteResideSql = "DELETE FROM reside WHERE dtlid = ?";
                            try (PreparedStatement deleteResidePs = con.prepareStatement(deleteResideSql)) {
                                deleteResidePs.setString(1, bookidNo);
                                deleteResidePs.executeUpdate();
                            }

                            // 第三步：更新 bookid 表的状态
                            String updateBookidSql = "UPDATE bookid SET status = 'o' WHERE id = ?";
                            try (PreparedStatement updateBookidPs = con.prepareStatement(updateBookidSql)) {
                                updateBookidPs.setString(1, rrid);
                                updateBookidPs.executeUpdate();
                            }
                        }
                    }
                }


            }

            // 执行删除操作
            ps = con.prepareStatement("DELETE FROM subscription_dtl WHERE id = ?");
            ps.setString(1, detailId);
            int rowsAffected = ps.executeUpdate();

            if (rowsAffected > 0) {
                resp.setContentType("text/html;charset=UTF-8");
                PrintWriter out = resp.getWriter();
                out.println("<script type='text/javascript'>");
                out.println("alert('删除成功！');");
                out.println("window.location.href='" + req.getContextPath() + "/viewOrderDetails.member?no=" + nno + "&id=" + detailId + "';");
                out.println("</script>");
                out.close();

            } else {
                // 删除失败的处理
                req.setAttribute("msg", "删除失败，记录未找到！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            req.setAttribute("msg", "删除过程中出现错误！");
        } finally {
            // 关闭资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
    // 处理入住添加
    protected void addReside(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String bookidNo = req.getParameter("bookidNo");
        String startDate = req.getParameter("startDate");
        String no = req.getParameter("no"); // 订单编号
        String bookidername = req.getParameter("bookidername");
        String idcard = req.getParameter("idcard");

        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            con = DriverManager.getConnection(url, "root", "root");

            ps = con.prepareStatement("INSERT INTO reside (dtlid, residedate, bookidername, idcard) VALUES (?, ?, ?, ?)");
            ps.setString(1, bookidNo);
            ps.setString(2, startDate);
            ps.setString(3, bookidername);
            ps.setString(4, idcard);
            ps.executeUpdate();
            resp.setContentType("text/html;charset=UTF-8");
            PrintWriter out = resp.getWriter();
            out.println("<script type='text/javascript'>");
            out.println("alert('修改成功！');");
            out.println("window.location.href='" + req.getContextPath() + "/viewOrderDetails.member?no=" + no + "';");
            out.println("</script>");
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
            resp.getWriter().write("数据库操作失败！");
        } finally {
            try { if (ps != null) ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (con != null) con.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
    protected void viewResideDetails(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String bookidNo = req.getParameter("bookidNo");

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            String un = "root";
            String pwd = "root";
            con = DriverManager.getConnection(url, un, pwd);

            ps = con.prepareStatement("SELECT bookidername, idcard FROM reside WHERE dtlid = ?");
            ps.setString(1, bookidNo);
            rs = ps.executeQuery();

            List<Reside> resideList = new ArrayList<>();

            while (rs.next()) {
                Reside reside = new Reside();
                reside.setBookiderName(rs.getString("bookidername"));
                reside.setIdCard(rs.getString("idcard"));
                resideList.add(reside);
            }

            // 设置为请求属性传给 JSP
            req.setAttribute("resideList", resideList);

            // 转发到 JSP 页面
            req.getRequestDispatcher("/hotel/manager/resideDetail.jsp").forward(req, resp);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    protected void updateOrderStatus(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String orderNo = req.getParameter("orderNo");
        String statusStr = req.getParameter("status");

        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/hotell?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
            con = DriverManager.getConnection(url, "root", "root");

            String sql = "UPDATE subscription SET status = ? WHERE no = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, Integer.parseInt(statusStr));
            ps.setString(2, orderNo);

            int updatedRows = ps.executeUpdate();

            if (updatedRows > 0) {
                req.setAttribute("msg", "订单状态更新成功！");
            } else {
                req.setAttribute("msg", "订单号无效，更新失败！");
            }

            resp.setContentType("text/html;charset=UTF-8");
            PrintWriter out = resp.getWriter();
            out.println("<script type='text/javascript'>");
            out.println("alert('修改成功！');");
            out.println("window.location.href='" + req.getContextPath() + "/viewOrderDetails.member?no=" + orderNo + "';");
            out.println("</script>");
            out.close();

        } catch (Exception e) {
            e.printStackTrace();
            req.setAttribute("msg", "更新订单状态失败！");
            req.getRequestDispatcher("/hotel/error.jsp").forward(req, resp);
        } finally {
            try {
                if (ps != null) ps.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
